package com.shi.Dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.shi.entity.GuideMessage;
import com.shi.entity.SellRecord;
import com.shi.entity.TravelAgency;
import com.shi.util.DBHelp;
import com.shi.util.RowMapper;
import com.shi.util.StringUtil;

public class SellDao {

	private DBHelp<SellRecord> db = new DBHelp<SellRecord>();
	public List<SellRecord> findSellList() {
		String sql = "select t_sellrecord.id, time,guide,travelagency,address,peopleNum,allsell,(allsell / peopleNum) AS averageMoney from t_sellrecord left join t_guidemessage on t_sellrecord.guideId = t_guidemessage.id left join t_travelagency on t_sellrecord.travelId = t_travelagency.id";
			Connection conn = db.connection();
			Statement stat = null;
			ResultSet rs = null;
				
			List<SellRecord> list = new ArrayList<SellRecord>();
			try {
				stat = conn.createStatement();
				rs = stat.executeQuery(sql);
				while(rs.next()){
					SellRecord sellrecord = new SellRecord();
					sellrecord.setId(rs.getInt("id"));
					sellrecord.setTime(rs.getString("time"));
					sellrecord.setTravel(rs.getString("travelagency"));
					sellrecord.setGuide(rs.getString("guide"));
					sellrecord.setAddress(rs.getString("address"));
					sellrecord.setPeopleNum(rs.getInt("peopleNum"));
					sellrecord.setAllsell(rs.getFloat("allsell"));
					sellrecord.setAverageMoney(rs.getFloat("averageMoney"));
					list.add(sellrecord);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				db.close(rs,stat,conn);
			}
			return list;
	}
	public void add(SellRecord sellrecord) {
		String sql = "insert into t_sellrecord(time,guideId,travelId,allsell,peopleNum,address) values(?,?,?,?,?,?)";
		db.excuteSQL(sql, sellrecord.getTime(),sellrecord.getGuidemessage().getId(),sellrecord.getTravelagency().getId(),sellrecord.getAllsell(),sellrecord.getPeopleNum(),sellrecord.getAddress());
	}
	public void update(SellRecord sellrecord) {
		String sql = "update t_sellrecord set time = ?,guideId = ?,travelId = ?,allsell = ?,peopleNum = ?,address = ? where id = ?";
		db.excuteSQL(sql, sellrecord.getTime(),sellrecord.getGuidemessage().getId(),sellrecord.getTravelagency().getId(),sellrecord.getAllsell(),sellrecord.getPeopleNum(),sellrecord.getAddress(),sellrecord.getId());
	}
	public void del(int id) {
		String sql = "delete from t_sellrecord where id = ?";
		db.excuteSQL(sql, id);
	}
	public SellRecord findbyId(int id) {
		String sql = "select t_sellrecord.id,time,guideId,guide,guideNum,travelId,travelagency,address,peopleNum,allsell,(allsell / peopleNum) AS averageMoney from t_sellrecord left join t_guidemessage on t_sellrecord.guideId = t_guidemessage.id left join t_travelagency on t_sellrecord.travelId = t_travelagency.id where t_sellrecord.id= ?";
		return db.executeSelect(sql,new SellRowMapper(),id);
	}
	public List<SellRecord> findWhere(String travel, String guide, String address,
			String minpeoplenum, String maxpeoplenum, String fromtime,
			String totime, String minallSell, String maxallSell) {
		String where = buildwhere(travel,guide,address,minpeoplenum,maxpeoplenum,fromtime,totime,minallSell,maxallSell);
		StringBuilder sql = new StringBuilder();
		sql.append( "select t_sellrecord.id, time,guide,travelagency,address,peopleNum,allsell,(allsell / peopleNum) AS averageMoney from t_sellrecord left join t_guidemessage on t_sellrecord.guideId = t_guidemessage.id left join t_travelagency on t_sellrecord.travelId = t_travelagency.id ");
		sql.append(where);
		sql.append("order by time desc");
		String sql0 = sql.toString();
		Connection conn = db.connection();
		Statement stat = null;
		ResultSet rs = null;
			
		List<SellRecord> list = new ArrayList<SellRecord>();
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql0);
			while(rs.next()){
				SellRecord sellrecord = new SellRecord();
				sellrecord.setId(rs.getInt("id"));
				sellrecord.setTime(rs.getString("time"));
				sellrecord.setTravel(rs.getString("travelagency"));
				sellrecord.setGuide(rs.getString("guide"));
				sellrecord.setAddress(rs.getString("address"));
				sellrecord.setPeopleNum(rs.getInt("peopleNum"));
				sellrecord.setAllsell(rs.getFloat("allsell"));
				sellrecord.setAverageMoney(rs.getFloat("averageMoney"));
				list.add(sellrecord);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(rs,stat,conn);
		}
		return list;
	}

	private String buildwhere(String travel, String guide, String address,
			String minpeoplenum, String maxpeoplenum, String fromtime,
			String totime, String minallSell, String maxallSell) {
		
		StringBuilder where = new StringBuilder();
		where.append("where ");
		if(StringUtil.isNotEmpty(travel)){
			where.append("travelagency = '" + travel + "' and ");
		}else if(guide != null){
			where.append("guide = '" + guide + "' and ");
		}else if(address != null){
			where.append("address = '" + address + "' and ");
		}else if(fromtime != null){
			where.append("time > '" + fromtime + "' and ");
		}else if(totime != null){
			where.append("time < '" + totime + "' and ");
		}else if(minallSell != null){
			float minallsell = Float.parseFloat(minallSell);
			where.append("allsell > '" + minallsell + "' and ");
		}else if(maxallSell != null){
			float maxallsell = Float.parseFloat(maxallSell);
			where.append("allsell < '" + maxallsell + "' and ");
		}else if(minpeoplenum != null){
			int minpeopleNum = Integer.parseInt(minpeoplenum);
			where.append("peopleNum > '" + minpeopleNum + "' and ");
		}else if(maxpeoplenum != null){
			int maxpeopleNum = Integer.parseInt(maxpeoplenum);
			where.append("peopleNum < '" + maxpeopleNum + "' and ");
		}
		
		String wheresql = where.toString();
		if(wheresql.endsWith("where ")){
			wheresql = "";
		}
		if(wheresql.endsWith("and ")){
			wheresql = wheresql.substring(0,wheresql.lastIndexOf("and "));
		}
		return wheresql;
	}

	class SellRowMapper implements RowMapper<SellRecord>{
		public SellRecord mapperRow(ResultSet rs) throws SQLException {
			SellRecord sellrecord = new SellRecord();
			sellrecord.setId(rs.getInt("id"));
			sellrecord.setTime(rs.getString("time"));
			sellrecord.setGuide(rs.getString("guide"));
			sellrecord.setGuideNum(rs.getString("guideNum"));
			sellrecord.setTravel(rs.getString("travelagency"));
			sellrecord.setAddress(rs.getString("address"));
			sellrecord.setPeopleNum(rs.getInt("peopleNum"));
			sellrecord.setAllsell(rs.getFloat("allsell"));
			sellrecord.setAverageMoney(rs.getFloat("averageMoney"));
			
			GuideMessage guidemessage = new GuideMessage();
			guidemessage.setId(rs.getString("id"));
			guidemessage.setGuide(rs.getString("guide"));
			guidemessage.setGuideNum(rs.getString("guideNum"));
			sellrecord.setGuidemessage(guidemessage);
			
			TravelAgency travelagency = new TravelAgency();
			travelagency.setId(rs.getString("travelId"));
			travelagency.setTravelAgency(rs.getString("travelagency"));
			sellrecord.setTravelagency(travelagency);
			
			return sellrecord;
		}
	}
}
